# pip install ebooklib BeautifulSoup4
from ebooklib import epub
from bs4 import BeautifulSoup
import mysql.connector


def aa(aa):
    file = "/Users/wsg/Desktop/qq.txt"
    with open(file, 'r', encoding='utf-8') as f:
        arr = f.readlines()
        for i in arr:
            if i.lower().find(aa.lower()) != -1:
                arr1 = [x.replace("\n", "") for x in i.split("/")]
                print(arr1[-3:])
        # print(len(arr),arr[0])


# aa('unity')

def readBook(file="/Users/wsg/Desktop/培养孩子的情商,从讲故事开始.epub"):
    f = file.replace(" ", "\\ ")
    try:
        book = epub.read_epub(f, options={"ignore_ncx": True})
        title = book.get_metadata('DC', 'title')[0][0]
        category = []
        texts = ""
        for x in book.items:
            if f"{type(x)}" == "<class 'ebooklib.epub.EpubHtml'>":
                soup = BeautifulSoup(x.content.decode("UTF-8"), features="html.parser")
                category += [x.get_text() for x in soup.findAll("a")]
                texts += soup.get_text().replace("'", "\'")
        print(f"{f}-sucess")
        return [title, "\n".join(sorted(list(set(category)))), texts]
    except KeyError:
        print(f"{f}-faild")
    except:
        print(f"{f}-faild")
    return [f, "error", ""]
    # print(book.items[-1] is epub.EpubNcx)


# with open(file, 'r',encoding='utf-8') as f:
#     print(f.read())

def dbQuery(a, sql, data={}):
    conn = mysql.connector.connect(
        host='localhost',
        user='root',
        password='11111111',
        database='ebook'
    )
    cursor = conn.cursor()
    a(cursor, conn, sql, data)
    cursor.close()
    conn.close()


def query(cursor, conn, sql, data):
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        print(row)


def commitQuery(cursor, conn, sql, data):
    # 执行SQL插入语句
    cursor.execute(sql, data)
    conn.commit()


def querySQL(sql):
    dbQuery(query, sql, {})


def insetSQL(sql, data):
    dbQuery(commitQuery, sql, data)


# querySQL("select * from book")
# insetSQL("INSERT INTO book (name,category,content) VALUES (%s,%s,%s)",readBook())

def bb():
    file = "/Users/wsg/Desktop/qq.txt"
    with open(file, 'r', encoding='utf-8') as f:
        for x in f.readlines():
            path = x.replace("\n", "")
            arr = readBook(path)
            if arr[1] != "error":
                insetSQL("INSERT INTO book (name,category,content) VALUES (%s,%s,%s)", arr)


bb()
